﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using System.Web;
using System.Net;
using System.Xml;
using System.Runtime.InteropServices;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Security.Cryptography;
using System.Globalization;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Threading;
using Log;
using Microsoft.Office.Interop.Excel;
namespace PayManage
{
    public static class DataService
    {
        public static SystemConfigs SysConfigs;
        public static ExcelSheetsConfig ExcelSheetsConfigs;
        public static bool IsLogin { get; set; }
        public static string UserName { get; set; }
        public static string fileName;
        private static readonly ILog logger = LogManager.GetLogger(typeof(DataService));
        /// <summary>
        /// 
        /// </summary>
        public static string sheetsFileName;

        #region 利用API实现窗体淡入淡出特效
        //窗体淡入淡出特效
        public const Int32 AW_HOR_POSITIVE = 0x00000001;
        public const Int32 AW_HOR_NEGATIVE = 0x00000002;
        public const Int32 AW_VER_POSITIVE = 0x00000004;
        public const Int32 AW_VER_NEGATIVE = 0x00000008;
        public const Int32 AW_CENTER = 0x00000010;
        public const Int32 AW_HIDE = 0x00010000;
        public const Int32 AW_ACTIVATE = 0x00020000;
        public const Int32 AW_SLIDE = 0x00040000;
        public const Int32 AW_BLEND = 0x00080000;
        [DllImport("user32.dll", CharSet = CharSet.Auto)]
        public static extern bool AnimateWindow(
        IntPtr hwnd,  //  handle  to  window    
        int dwTime,  //  duration  of  animation    
        int dwFlags  //  animation  type    
        );
        #endregion

        static DataService()
        {
            fileName = AppDomain.CurrentDomain.BaseDirectory + "\\" + "sysrun.dll";
            sheetsFileName = AppDomain.CurrentDomain.BaseDirectory + "\\" + "sheets.dll";
            //避免覆盖
            //SysConfigs = new SystemConfigs();
            //ExcelSheetsConfigs = new ExcelSheetsConfig();
            //读取默认的配置文件
            if (!File.Exists(fileName))
            {
                CreateConfig();
            }
            else
            {
                try
                {
                    SysConfigs = Serializer.ConvertFromXmlFile<SystemConfigs>(fileName);
                }
                catch (Exception ex)
                {
                    logger.Error(ex.Message);
                    //CreateConfig();
                }

            }

            //读取默认的配置文件
            if (!File.Exists(sheetsFileName))
            {
                CreateSheetsConfig();
            }
            else
            {
                try
                {
                    ExcelSheetsConfigs = Serializer.ConvertFromXmlFile<ExcelSheetsConfig>(sheetsFileName);
                }
                catch (Exception ex)
                {
                    logger.Error(ex.Message);
                }
            }
        }

        public static void CreateConfig()
        {
            SysConfigs.Users.Add(new UsersConfigEntity("meijuan", "彭美娟", Encode("pmjpcl")));
            Serializer.ConvertToXmlFile(SysConfigs, fileName);
        }
        /// <summary>
        /// 没有配置，默认创建一个
        /// </summary>
        public static void CreateSheetsConfig()
        {
            var sheet = new SheetConfigEntity();
            sheet.SheetName = "Sheet1";
            sheet.Columns.Add(new ColumnConfigEntity());
            ExcelSheetsConfigs.Sheets.Add(sheet);
            Serializer.ConvertToXmlFile(ExcelSheetsConfigs, sheetsFileName);
        }
        public static void Save()
        {
            if (SysConfigs != null && SysConfigs.Users.Count > 0)
                Serializer.ConvertToXmlFile(SysConfigs, fileName);
        }
        public static void Load()
        {
            SysConfigs = Serializer.ConvertFromXmlFile<SystemConfigs>(fileName);
        }
        public static void SaveSheets()
        {
            if (ExcelSheetsConfigs != null && ExcelSheetsConfigs.Sheets.Count > 0)
            {
                Serializer.ConvertToXmlFile(ExcelSheetsConfigs, sheetsFileName);
            }
        }
        public static void LoadSheets()
        {
            ExcelSheetsConfigs = Serializer.ConvertFromXmlFile<ExcelSheetsConfig>(sheetsFileName);
        }

        public static void EduceExcel(string fileName, out List<string> sheetList)
        {
            //return;
            string strConnection = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + fileName.ToString() + ";Extended Properties=Excel 8.0";
            sheetList = new List<string>();
            try
            {
                using (OleDbConnection conn = new OleDbConnection())
                {
                    try
                    {
                        try
                        {
                            conn.ConnectionString = strConnection;
                            conn.Open();
                        }
                        catch
                        {
                            strConnection = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + fileName.ToString() + ";Extended Properties=Excel 9.0;HDR=no";
                            conn.ConnectionString = strConnection;
                            conn.Open();
                        }

                        System.Data.DataTable dataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        //获得所有表中的dataset
                        Dictionary<string, DataSet> dicTablesData = new Dictionary<string, DataSet>();
                        for (int i = 0; i < dataTable.Rows.Count; i++)
                        {
                            string strSheetName = dataTable.Rows[i][2].ToString().Trim();
                            // if (keywords.FirstOrDefault((s) => strSheetName.IndexOf(s) != -1) != string.Empty)
                            //{
                            string strExcel = "select * from [" + strSheetName + "]";
                            DataSet ds = new DataSet();
                            OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConnection);
                            adapter.Fill(ds, strSheetName);
                            dicTablesData.Add(strSheetName, ds);
                            int colCout = ds.Tables[0].Columns.Count;
                            string result = "";
                            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                            {
                                result += ds.Tables[0].Columns[j].ColumnName + "\r\n";
                            }
                            //}
                        }
                    }
                    catch (Exception exx)
                    {
                        ShowErrorMsg(exx.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ShowErrorMsg(ex.Message);
            }


        }

        public static bool ReadExcelSheet(string strFilePath, string sheetName)
        {
            bool result = false;
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            //Microsoft.Office.Interop.Excel.Workbooks xlWorkbooks;
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook;// 这里抛出异常= new Microsoft.Office.Interop.Excel.Workbook();
            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
            Microsoft.Office.Interop.Excel.Range range;
            //System.Type tyWorkbooks;
            //System.Reflection.MethodInfo[] methods;
            object objFilePath;
            object oMissing = System.Reflection.Missing.Value;

            if (!System.IO.File.Exists(strFilePath))
            {
                throw new System.IO.FileNotFoundException();
            }
            try
            {
                xlApp.Visible = false;
                //xlWorkbooks = xlApp.Workbooks;
                //tyWorkbooks = xlWorkbooks.GetType();
                //methods = tyWorkbooks.GetMethods();
                objFilePath = strFilePath;
                object Nothing = System.Reflection.Missing.Value;
                object password = (object)DataService.SysConfigs.ExcelPassword;
                xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, password, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);

                List<string> allsheets = new List<string>();
                for (int i = 1; i <= xlWorkbook.Sheets.Count; i++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[i];
                    allsheets.Add(wsheet.Name);
                }

                //xlWorkbook = (Microsoft.Office.Interop.Excel.Workbook)tyWorkbooks.InvokeMember("Open ",
                //System.Reflection.BindingFlags.InvokeMethod,
                //null,
                //xlWorkbooks,
                //new object[] { objFilePath, true, true });


                //打开文件中的在配置信息中的表数据
                Dictionary<string, System.Data.DataTable> dicdataTable = (Dictionary<string, System.Data.DataTable>)Caching.Cache[strFilePath];
                if (dicdataTable == null)
                    dicdataTable = new Dictionary<string, System.Data.DataTable>();
                //根据表名查找表 这里如果表不存在会抛异常    
                var sheetconfig = DataService.ExcelSheetsConfigs.Sheets.FirstOrDefault((s) => s.SheetName.Equals(sheetName));
                if (allsheets.IndexOf(sheetName) != -1)
                {
                    xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[sheetconfig.SheetName];
                    System.Data.DataTable dt = new System.Data.DataTable();
                    CreatTableByColumns(sheetconfig.SheetName, ref dt);
                    int rowCount = xlWorksheet.UsedRange.Rows.Count;
                    int rowStart = sheetconfig.StartRowIndex > 0 ? sheetconfig.StartRowIndex : 1;
                    int colCount = sheetconfig.Columns.Count;

                    //选择区域得到数据数组
                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.get_Range("A" + rowStart.ToString(), strUtil.GetExcelColName(colCount) + rowStart.ToString());
                    //Array array = range.Cells.Value2 as Array;

                    for (int i = rowStart; i <= rowCount; i++)
                    {
                        var header = ((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, 1]).Value2;
                        if (header == null || string.IsNullOrEmpty(header.ToString()))
                            break;
                        DataRow dr = dt.NewRow();
                        for (int j = 1; j <= colCount; j++)
                        {
                            range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                            //result += string.Format("\t", j);
                            if (range.Cells.Value2 == null)
                                continue;
                            var colType = dt.Columns[j - 1].DataType;
                            if (colType == typeof(DateTime))
                            {
                                dr[j - 1] = DateTime.FromOADate((double)range.Cells.Value2);
                            }
                            else if (colType == typeof(int))
                            {
                                dr[j - 1] = cvt.ToInt(range.Cells.Value2);
                            }
                            else if (colType == typeof(double))
                            {
                                dr[j - 1] = cvt.ToDouble(range.Cells.Value2.ToString());
                            }
                            else
                            {
                                dr[j - 1] = cvt.ToNotNull(range.Cells.Value2);
                            }
                            // result += range.Cells.Value2.ToString();
                        }
                        dt.Rows.Add(dr);
                        // result += string.Format("\r\n");
                    }
                    dicdataTable[sheetconfig.SheetName] = dt;
                    Caching.Cache[strFilePath + sheetName] = dt.Rows.Count;
                }
                else
                {
                    dicdataTable[sheetconfig.SheetName] = null;
                    Caching.Cache[strFilePath + sheetName] = 0;
                }

                //xlWorkbook.Save();  

                Caching.Cache[strFilePath] = dicdataTable;
                xlWorkbook.Close(oMissing, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);

            }
            catch (Exception ex)
            {
                result = false;
                logger.Error(ex.Message);
                throw ex;

            }
            finally
            {

                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();

            }
            return result;
        }


        public static bool ReadExcel(string strFilePath, string sheetName, out string tableName)
        {
            bool result = false;
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook;// 这里抛出异常= new Microsoft.Office.Interop.Excel.Workbook();
            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
            Microsoft.Office.Interop.Excel.Range range;
            object objFilePath;
            object oMissing = System.Reflection.Missing.Value;

            if (!System.IO.File.Exists(strFilePath))
            {
                throw new System.IO.FileNotFoundException();
            }
            try
            {
                xlApp.Visible = false;
                objFilePath = strFilePath;
                object Nothing = System.Reflection.Missing.Value;
                object password = (object)DataService.SysConfigs.ExcelPassword;
                xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, password, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);

                List<string> allsheets = new List<string>();
                for (int i = 1; i <= xlWorkbook.Sheets.Count; i++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[i];
                    allsheets.Add(wsheet.Name);
                }


                //打开文件中的在配置信息中的表数据

                if (string.IsNullOrEmpty(sheetName))
                {
                    xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[1];
                }
                else if (allsheets.IndexOf(sheetName) != -1)
                {
                    xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[sheetName];
                }
                else
                {
                    throw new Exception("not find " + sheetName);
                }
                tableName = xlWorksheet.Name;
                Dictionary<string, System.Data.DataTable> dicdataTable = (Dictionary<string, System.Data.DataTable>)Caching.Cache[strFilePath];
                if (dicdataTable == null)
                    dicdataTable = new Dictionary<string, System.Data.DataTable>();

                //int rowCount = xlWorksheet.UsedRange.Rows.Count;
                //int rowCount = 80;
                int rowCount = 474;
                int rowStart = 1;
                int colCount = 36;

                System.Data.DataTable dt = new System.Data.DataTable();
                for (int i = 0; i < colCount; i++)
                {
                    dt.Columns.Add(i.ToString(), typeof(object));
                }

                for (int i = rowStart; i <= rowCount; i++)
                {
                    DataRow dr = dt.NewRow();
                    for (int j = 1; j <= colCount; j++)
                    {
                        range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                        if (range.Cells.Value2 == null)
                            continue;
                        dr[j - 1] = range.Cells.Value2;
                    }
                    dt.Rows.Add(dr);
                }
                dicdataTable[tableName] = dt;
                Caching.Cache[strFilePath] = dicdataTable;
                xlWorkbook.Close(oMissing, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);

            }
            catch (Exception ex)
            {
                result = false;
                logger.Error(ex.Message);
                throw ex;

            }
            finally
            {

                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();

            }
            return result;
        }
        /// <summary>
        /// 更新Excel单元格数据
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <param name="strSheetName"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="cellvalue"></param>
        public static void UpdateSheet(string strFilePath, string strSheetName, int row, int col, string cellvalue)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbooks xlWorkbooks;
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
            System.Type tyWorkbooks;
            System.Reflection.MethodInfo[] methods;
            object objFilePath;
            object oMissing = System.Reflection.Missing.Value;

            if (!System.IO.File.Exists(strFilePath))
            {
                throw new System.IO.FileNotFoundException();
                return;
            }
            try
            {
                xlApp.Visible = false;
                xlWorkbooks = xlApp.Workbooks;
                tyWorkbooks = xlWorkbooks.GetType();
                methods = tyWorkbooks.GetMethods();
                objFilePath = strFilePath;
                object Nothing = System.Reflection.Missing.Value;
                object password = (object)DataService.SysConfigs.ExcelPassword;
                xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, password, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);

                //xlWorkbook = (Microsoft.Office.Interop.Excel.Workbook)tyWorkbooks.InvokeMember("Open ",
                //System.Reflection.BindingFlags.InvokeMethod,
                //null,
                //xlWorkbooks,
                //new object[] { objFilePath, true, true });

                xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[strSheetName];
                //Microsoft.Office.Interop.Excel.Worksheet B = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets["B"];
                //Microsoft.Office.Interop.Excel.Worksheet C = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets["C"];
                xlWorksheet.Cells[row, col] = cellvalue;
                xlWorkbook.Save();
                xlWorkbook.Close(oMissing, oMissing, oMissing);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (xlApp != null)
                {
                    xlApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();
            }
        }

        /// <summary>
        /// 根据单元格查找excel数据
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <param name="strSheetName"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        public static string GetCellValue(string strFilePath, string strSheetName, int row, string col)
        {
            string result = "";
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbooks xlWorkbooks;
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
            Microsoft.Office.Interop.Excel.Range range;
            System.Type tyWorkbooks;
            System.Reflection.MethodInfo[] methods;
            object objFilePath;
            object oMissing = System.Reflection.Missing.Value;

            if (!System.IO.File.Exists(strFilePath))
            {
                throw new System.IO.FileNotFoundException();
            }
            try
            {
                xlApp.Visible = false;
                xlWorkbooks = xlApp.Workbooks;
                tyWorkbooks = xlWorkbooks.GetType();
                methods = tyWorkbooks.GetMethods();
                objFilePath = strFilePath;
                object Nothing = System.Reflection.Missing.Value;
                object password = (object)DataService.SysConfigs.ExcelPassword;
                xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, password, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);

                xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[strSheetName];

                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[9, 14];


                //xlWorkbook.Save();
                xlWorkbook.Close(oMissing, oMissing, oMissing);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (xlApp != null)
                {
                    xlApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();

            }
            return result;
        }

        /// <summary>
        /// 打开Excel文件，并且根据配置表中表名一次性加载内容都缓存中去 
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <param name="strSheetName"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        public static bool OpenExcelFile(string strFilePath, out List<string> sheetList)
        {
            bool result = false;
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook;// 这里抛出异常= new Microsoft.Office.Interop.Excel.Workbook();
            //Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
            //Microsoft.Office.Interop.Excel.Range range;     
            object objFilePath;
            object oMissing = System.Reflection.Missing.Value;
            sheetList = new List<string>();

            if (!System.IO.File.Exists(strFilePath))
            {
                throw new System.IO.FileNotFoundException();
            }
            try
            {
                xlApp.Visible = false;
                objFilePath = strFilePath;
                object Nothing = System.Reflection.Missing.Value;
                object password = (object)DataService.SysConfigs.ExcelPassword;
                xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, password, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
                List<string> allsheets = new List<string>();
                for (int i = 1; i <= xlWorkbook.Sheets.Count; i++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[i];
                    allsheets.Add(wsheet.Name);
                }

                //xlWorkbook = (Microsoft.Office.Interop.Excel.Workbook)tyWorkbooks.InvokeMember("Open ",
                //System.Reflection.BindingFlags.InvokeMethod,
                //null,
                //xlWorkbooks,
                //new object[] { objFilePath, true, true });


                //打开文件中的在配置信息中的表数据
                //Dictionary<string, System.Data.DataTable> dicdataTable = new Dictionary<string, System.Data.DataTable>();
                foreach (var sheetconfig in DataService.ExcelSheetsConfigs.Sheets)
                {
                    if (allsheets.IndexOf(sheetconfig.SheetName) != -1)
                    {
                        sheetList.Add(sheetconfig.SheetName);
                    }
                    /*
                    //根据表名查找表 这里如果表不存在会抛异常                 
                    if (allsheets.IndexOf(sheetconfig.SheetName) != -1)
                    {
                        xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[sheetconfig.SheetName];
                        System.Data.DataTable dt = new System.Data.DataTable();
                        CreatTableByColumns(sheetconfig.SheetName, ref dt);
                        int rowCount = xlWorksheet.UsedRange.Rows.Count;
                        int rowStart = sheetconfig.StartRowIndex > 0 ? sheetconfig.StartRowIndex : 1;
                        int colCount = sheetconfig.Columns.Count;

                        //选择区域得到数据数组
                        //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.get_Range("A" + rowStart.ToString(), strUtil.GetExcelColName(colCount) + rowStart.ToString());
                        //Array array = range.Cells.Value2 as Array;

                        for (int i = rowStart; i <= rowCount; i++)
                        {
                            var header = ((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, 1]).Value2;
                            if (header == null || string.IsNullOrEmpty(header.ToString()))
                                break;
                            DataRow dr = dt.NewRow();
                            for (int j = 1; j <= colCount; j++)
                            {
                                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                //result += string.Format("\t", j);
                                if (range.Cells.Value2 == null)
                                    continue;
                                var colType = dt.Columns[j - 1].DataType;
                                if (colType == typeof(DateTime))
                                {
                                    dr[j - 1] = DateTime.FromOADate((double)range.Cells.Value2);
                                }
                                else if (colType == typeof(int))
                                {
                                    dr[j - 1] = cvt.ToInt(range.Cells.Value2);
                                }
                                else if (colType == typeof(double))
                                {
                                    dr[j - 1] = cvt.ToDouble(range.Cells.Value2.ToString());
                                }
                                else
                                {
                                    dr[j - 1] = cvt.ToNotNull(range.Cells.Value2);
                                }
                                // result += range.Cells.Value2.ToString();
                            }
                            dt.Rows.Add(dr);
                            // result += string.Format("\r\n");
                        }
                       // dicdataTable[sheetconfig.SheetName] = dt;
                    }
                    else
                    {
                        //dicdataTable[sheetconfig.SheetName] = null;
                    }
                     */
                }
                if (DataService.SysConfigs.ExcelFiles.IndexOf(strFilePath) == -1)
                {
                    DataService.SysConfigs.ExcelFiles.Add(strFilePath);
                }
                xlWorkbook.Close(oMissing, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);

            }
            catch (Exception ex)
            {
                result = false;
                logger.Error(ex.Message);
                throw ex;

            }
            finally
            {

                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();

            }
            return result;
        }


        /// <summary>
        /// 原文件保存
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <param name="currentSheetName"></param>
        /// <param name="dgv"></param>
        /// <returns></returns>
        public static bool SaveExcelFile(string strFilePath, string currentSheetName, DataGridView dgv)
        {
            bool result = false;
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            //Microsoft.Office.Interop.Excel.Workbooks xlWorkbooks;
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook; // 这里抛出异常= new Microsoft.Office.Interop.Excel.Workbook();
            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
            Microsoft.Office.Interop.Excel.Range range;

            object objFilePath;
            object oMissing = System.Reflection.Missing.Value;

            if (!System.IO.File.Exists(strFilePath))
            {
                throw new System.IO.FileNotFoundException();
            }
            try
            {
                var sheetconfig = ExcelSheetsConfigs.Sheets.FirstOrDefault((s) => s.SheetName == currentSheetName);
                if (sheetconfig == null)
                {
                    //logger.Error("not find sheet in config");
                    //throw new Exception("not find sheet in config");
                    return true;
                }
                xlApp.Visible = false;
                xlApp.AlertBeforeOverwriting = false;
                objFilePath = strFilePath;
                object Nothing = System.Reflection.Missing.Value;
                object password = (object)DataService.SysConfigs.ExcelPassword;
                xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, password, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);

                //防止提示密码
                //xlWorkbook.Password = DataService.SysConfigs.ExcelPassword;
                List<string> allsheets = new List<string>();
                for (int i = 1; i <= xlWorkbook.Sheets.Count; i++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[i];
                    allsheets.Add(wsheet.Name);
                }


                //根据表名查找表 这里如果表不存在会抛异常                 
                if (allsheets.IndexOf(currentSheetName) != -1)
                {
                    xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[sheetconfig.SheetName];
                    System.Data.DataTable dt = (System.Data.DataTable)dgv.DataSource;

                    //数据总行数
                    int rowCount = dt.Rows.Count;
                    int rowStart = sheetconfig.StartRowIndex > 0 ? sheetconfig.StartRowIndex : 1;
                    int colCount = sheetconfig.Columns.Count;
                    int originalRowsCount = Convert.ToInt32(Caching.Cache[strFilePath + currentSheetName]);

                    //选择区域得到数据数组
                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.get_Range("A" + rowStart.ToString(), strUtil.GetExcelColName(colCount) + rowStart.ToString());
                    //Array array = range.Cells.Value2 as Array;
                    for (int j = 1; j <= colCount; j++)
                    {
                        var colconfig = sheetconfig.Columns.FirstOrDefault((c) => c.Id == dt.Columns[j - 1].ColumnName);
                        if (colconfig == null)
                            continue;
                        //   //需要公式则，直接填充如=F4+G4 'BasicPay'+'JobPayAdd'  B2+14     

                        if (colconfig.MustMethod)
                        {

                            for (int i = rowStart; i < rowCount + rowStart; i++)
                            {
                                string method = Regex.Replace(colconfig.CountMethod, @"'[A-Za-z0-9]*'", (match) => strUtil.GetExcelColName(sheetconfig.Columns.FindIndex((c) => c.Id == match.Value.Trim('\'')) + 1) + i);
                                //range.Cells.Value2 = "=" + method;
                                if (j == 1 && i - rowStart + 1 > originalRowsCount)
                                {
                                    range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Rows[i, oMissing];
                                    //range.Select();
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i - 1, j];
                                    //range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //插入新列
                                    //range.Select();
                                    //range.Copy(oMissing);
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                    //range.Select();
                                    range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

                                }
                                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                xlWorksheet.Cells[i, j] = "=" + method;
                            }


                        }

                        //该列既可以保存公式,又可以保存值,保存的时候，如果值！=计算的值，优先保存值
                        else if (colconfig.SetOrMethod)
                        {
                            //判断单元格内容与计算公式的比较
                            for (int i = rowStart; i < rowCount + rowStart; i++)
                            {

                                var cellvalue = cvt.ToDouble(dt.Rows[i - rowStart][j - 1].ToString());
                                //公式再次计算
                                string method = Regex.Replace(colconfig.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(dt.Rows[i - rowStart][match.Value.Trim('\'')].ToString()).ToString());
                                var countvalue = cvt.ToDouble(strUtil.qswhEval1(method).ToString());
                                if (j == 1 && i - rowStart + 1 > originalRowsCount)
                                {
                                    range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Rows[i, oMissing];
                                    //range.Select();
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i-1, j];
                                    //range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //插入新列
                                    //range.Select();
                                    //range.Copy(oMissing);
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                    //range.Select();
                                    range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                                }

                                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                if (cellvalue.ToString("f2") != countvalue.ToString("f2"))
                                {
                                    range.Cells.Value2 = dt.Rows[i - rowStart][j - 1];
                                }
                                else
                                {
                                    method = Regex.Replace(colconfig.CountMethod, @"'[A-Za-z0-9]*'", (match) => strUtil.GetExcelColName(sheetconfig.Columns.FindIndex((c) => c.Id == match.Value.Trim('\'')) + 1) + i);
                                    xlWorksheet.Cells[i, j] = "=" + method;
                                }
                            }
                        }
                        //不需要公式的列
                        else
                        {
                            for (int i = rowStart; i < rowCount + rowStart; i++)
                            {
                                //dgv.Rows[i].State


                                if (j == 1 && i - rowStart + 1 > originalRowsCount)
                                {
                                    range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Rows[i, oMissing];
                                    //range.Select();
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i - 1, j];
                                    //range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //插入新列
                                    //range.Select();
                                    //range.Copy(oMissing);
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                    //range.Select();
                                    range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

                                }
                                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                range.Cells.Value2 = dt.Rows[i - rowStart][j - 1];
                            }

                        }

                    }
                    //如果行减少，则删除
                    if (rowCount < originalRowsCount)
                    {
                        int deletecount = originalRowsCount - rowCount;
                        for (int i = 0; i < deletecount; i++)
                        {
                            range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Rows[rowStart + rowCount, oMissing];
                            //range.Select();
                            range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
                        }

                    }

                    for (int j = 1; j < colCount; j++)
                    {
                        //下面底部带公式
                        range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowCount + rowStart, j];
                        var summethod = range.Formula.ToString();
                        if (summethod.StartsWith("=") && summethod.IndexOf(":") != -1)
                        {
                            string[] temp = summethod.Split(':');
                            summethod = Regex.Replace(temp[0], "\\d+", rowStart.ToString()) + ":" + Regex.Replace(temp[1], "\\d+", (rowCount + rowStart - 1).ToString());
                            xlWorksheet.Cells[rowCount + rowStart, j] = summethod;
                        }
                    }

                    //同时保存到缓存
                    ((Dictionary<string, System.Data.DataTable>)Caching.Cache[strFilePath])[currentSheetName] = dt;
                    //改变缓存里是行数
                    Caching.Cache[strFilePath + currentSheetName] = dt.Rows.Count;
                }

                //保存并关闭
                xlWorkbook.Save();
                xlWorkbook.Close(oMissing, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
                result = true;
            }
            catch (Exception ex)
            {
                result = false;
                logger.Error(ex.Message);
                throw ex;

            }
            finally
            {

                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();

            }
            return result;
        }


        /// <summary>
        /// 另存为
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <param name="currentSheetName"></param>
        /// <param name="dgv"></param>
        /// <returns></returns>
        public static bool SaveAsExcelFile(string strFilePath, string currentSheetName, DataGridView dgv, string newFilePath)
        {
            bool result = false;
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            //Microsoft.Office.Interop.Excel.Workbooks xlWorkbooks;
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook; // 这里抛出异常= new Microsoft.Office.Interop.Excel.Workbook();
            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
            Microsoft.Office.Interop.Excel.Range range;

            object objFilePath;
            object oMissing = System.Reflection.Missing.Value;

            if (!System.IO.File.Exists(strFilePath))
            {
                throw new System.IO.FileNotFoundException();
            }

            try
            {
                if (System.IO.File.Exists(newFilePath))
                {
                    File.Delete(newFilePath);
                }
                File.Copy(strFilePath, newFilePath);

                var sheetconfig = ExcelSheetsConfigs.Sheets.FirstOrDefault((s) => s.SheetName == currentSheetName);
                if (sheetconfig == null)
                {
                    //logger.Error("not find sheet in config");
                    //throw new Exception("not find sheet in config");
                    return true;
                }
                //strFilePath = newFilePath;
                xlApp.Visible = false;
                xlApp.AlertBeforeOverwriting = false;
                objFilePath = strFilePath;
                object Nothing = System.Reflection.Missing.Value;
                object password = (object)DataService.SysConfigs.ExcelPassword;
                xlWorkbook = xlApp.Workbooks.Open(newFilePath, Nothing, Nothing, Nothing, password, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
                List<string> allsheets = new List<string>();
                for (int i = 1; i <= xlWorkbook.Sheets.Count; i++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[i];
                    allsheets.Add(wsheet.Name);
                }


                //根据表名查找表 这里如果表不存在会抛异常                 
                if (allsheets.IndexOf(currentSheetName) != -1)
                {
                    xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[sheetconfig.SheetName];
                    System.Data.DataTable dt = (System.Data.DataTable)dgv.DataSource;

                    //数据总行数
                    int rowCount = dt.Rows.Count;
                    int rowStart = sheetconfig.StartRowIndex > 0 ? sheetconfig.StartRowIndex : 1;
                    int colCount = sheetconfig.Columns.Count;
                    int originalRowsCount = Convert.ToInt32(Caching.Cache[strFilePath + currentSheetName]);

                    //选择区域得到数据数组
                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.get_Range("A" + rowStart.ToString(), strUtil.GetExcelColName(colCount) + rowStart.ToString());
                    //Array array = range.Cells.Value2 as Array;
                    for (int j = 1; j <= colCount; j++)
                    {
                        var colconfig = sheetconfig.Columns.FirstOrDefault((c) => c.Id == dt.Columns[j - 1].ColumnName);
                        if (colconfig == null)
                            continue;
                        //   //需要公式则，直接填充如=F4+G4 'BasicPay'+'JobPayAdd'  B2+14     

                        if (colconfig.MustMethod)
                        {

                            for (int i = rowStart; i < rowCount + rowStart; i++)
                            {
                                string method = Regex.Replace(colconfig.CountMethod, @"'[A-Za-z0-9]*'", (match) => strUtil.GetExcelColName(sheetconfig.Columns.FindIndex((c) => c.Id == match.Value.Trim('\'')) + 1) + i);
                                //range.Cells.Value2 = "=" + method;
                                if (j == 1 && i - rowStart + 1 > originalRowsCount)
                                {
                                    range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Rows[i, oMissing];
                                    range.Select();
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i - 1, j];
                                    //range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //插入新列
                                    //range.Select();
                                    //range.Copy(oMissing);
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                    //range.Select();
                                    range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

                                }
                                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                xlWorksheet.Cells[i, j] = "=" + method;
                            }


                        }

                        //该列既可以保存公式,又可以保存值,保存的时候，如果值！=计算的值，优先保存值
                        else if (colconfig.SetOrMethod)
                        {
                            //判断单元格内容与计算公式的比较
                            for (int i = rowStart; i < rowCount + rowStart; i++)
                            {

                                var cellvalue = cvt.ToDouble(dt.Rows[i - rowStart][j - 1].ToString());
                                //公式再次计算
                                string method = Regex.Replace(colconfig.CountMethod, @"'[A-Za-z0-9]*'", (match) => cvt.ToDouble(dt.Rows[i - rowStart][match.Value.Trim('\'')].ToString()).ToString());
                                var countvalue = cvt.ToDouble(strUtil.qswhEval1(method).ToString());
                                if (j == 1 && i - rowStart + 1 > originalRowsCount)
                                {
                                    range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Rows[i, oMissing];
                                    range.Select();
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i-1, j];
                                    //range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //插入新列
                                    //range.Select();
                                    //range.Copy(oMissing);
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                    //range.Select();
                                    range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                                }

                                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                if (cellvalue.ToString("f2") != countvalue.ToString("f2"))
                                {
                                    range.Cells.Value2 = dt.Rows[i - rowStart][j - 1];
                                }
                                else
                                {
                                    method = Regex.Replace(colconfig.CountMethod, @"'[A-Za-z0-9]*'", (match) => strUtil.GetExcelColName(sheetconfig.Columns.FindIndex((c) => c.Id == match.Value.Trim('\'')) + 1) + i);
                                    xlWorksheet.Cells[i, j] = "=" + method;
                                }
                            }
                        }
                        //不需要公式的列
                        else
                        {
                            for (int i = rowStart; i < rowCount + rowStart; i++)
                            {
                                //dgv.Rows[i].State


                                if (j == 1 && i - rowStart + 1 > originalRowsCount)
                                {
                                    range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Rows[i, oMissing];
                                    range.Select();
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i - 1, j];
                                    //range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //插入新列
                                    //range.Select();
                                    //range.Copy(oMissing);
                                    //range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                    //range.Select();
                                    range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

                                }
                                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, j];
                                range.Cells.Value2 = dt.Rows[i - rowStart][j - 1];
                            }

                        }
                    }
                    //如果行减少，则删除
                    if (rowCount < originalRowsCount)
                    {
                        int deletecount = originalRowsCount - rowCount;
                        for (int i = 0; i < deletecount; i++)
                        {
                            range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Rows[rowStart + rowCount, oMissing];
                            //range.Select();
                            range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
                        }

                    }

                    for (int j = 1; j < colCount; j++)
                    {
                        //下面底部带公式
                        range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowCount + rowStart, j];
                        var summethod = range.Formula.ToString();
                        if (summethod.StartsWith("=") && summethod.IndexOf(":") != -1)
                        {
                            string[] temp = summethod.Split(':');
                            summethod = Regex.Replace(temp[0], "\\d+", rowStart.ToString()) + ":" + Regex.Replace(temp[1], "\\d+", (rowCount + rowStart - 1).ToString());
                            xlWorksheet.Cells[rowCount + rowStart, j] = summethod;
                        }
                    }

                    //同时保存到缓存
                    Dictionary<string, System.Data.DataTable> dic = (Dictionary<string, System.Data.DataTable>)Caching.Cache[newFilePath];
                    if (dic == null)
                        dic = new Dictionary<string, System.Data.DataTable>();
                    dic[currentSheetName] = dt;
                    Caching.Cache[newFilePath] = dic;
                    //改变缓存里是行数
                    Caching.Cache[newFilePath + currentSheetName] = dt.Rows.Count;
                }

                //保存并关闭
                xlWorkbook.Save();
                xlWorkbook.Close(oMissing, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
                result = true;
            }
            catch (Exception ex)
            {
                result = false;
                logger.Error(ex.Message);
                throw ex;

            }
            finally
            {

                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();

            }
            return result;
        }

        /// <summary>
        /// 测试删除某行
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="rowindex"></param>
        /// <returns></returns>
        public static bool DeleteExcel(string strFilePath, string sheetName, int rowindex)
        {
            bool result = false;
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook;// 这里抛出异常= new Microsoft.Office.Interop.Excel.Workbook();
            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
            Microsoft.Office.Interop.Excel.Range range;
            //System.Type tyWorkbooks;
            //System.Reflection.MethodInfo[] methods;
            object objFilePath;
            object oMissing = System.Reflection.Missing.Value;

            if (!System.IO.File.Exists(strFilePath))
            {
                throw new System.IO.FileNotFoundException();
            }
            try
            {
                xlApp.Visible = false;
                xlApp.DisplayAlerts = false;
                objFilePath = strFilePath;
                object Nothing = System.Reflection.Missing.Value;
                object password = (object)DataService.SysConfigs.ExcelPassword;
                xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, password, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);

                xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[sheetName];
                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Rows[rowindex, oMissing];
                range.Select();
                range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
                xlWorkbook.Save();
                xlWorkbook.Close(oMissing, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);

            }
            catch (Exception ex)
            {
                result = false;
                logger.Error(ex.Message);
                throw ex;

            }
            finally
            {

                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();

            }
            return result;
        }


        /// <summary>
        /// 测试删除某行
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="rowindex"></param>
        /// <returns></returns>
        public static bool InsertRowExcel(string strFilePath, string sheetName, int rowindex)
        {
            bool result = false;
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook;// 这里抛出异常= new Microsoft.Office.Interop.Excel.Workbook();
            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
            Microsoft.Office.Interop.Excel.Range range;
            //System.Type tyWorkbooks;
            //System.Reflection.MethodInfo[] methods;
            object objFilePath;
            object oMissing = System.Reflection.Missing.Value;

            if (!System.IO.File.Exists(strFilePath))
            {
                throw new System.IO.FileNotFoundException();
            }
            try
            {
                xlApp.Visible = false;
                xlApp.DisplayAlerts = false;
                objFilePath = strFilePath;
                object Nothing = System.Reflection.Missing.Value;
                object password = (object)DataService.SysConfigs.ExcelPassword;
                xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, password, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);

                xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[sheetName];
                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Rows[rowindex, oMissing];
                range.Select();
                range.Insert(oMissing, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                range = (Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[rowindex, 1];
                range.Value2 = "第" + rowindex + "行" + DateTime.Now.ToShortTimeString();
                xlWorkbook.Save();
                xlWorkbook.Close(oMissing, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);

            }
            catch (Exception ex)
            {
                result = false;
                logger.Error(ex.Message);
                throw ex;

            }
            finally
            {

                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();

            }
            return result;
        }

        /// <summary>
        /// 根据表名创建表的结构
        /// </summary>
        /// <param name="sheetConfigName"></param>
        /// <param name="dt"></param>
        public static void CreatTableByColumns(string sheetConfigName, ref System.Data.DataTable dt)
        {
            dt.Columns.Clear();
            try
            {
                List<ColumnConfigEntity> cols = GetColumnsEntityByName(sheetConfigName);
                foreach (var col in cols)
                {
                    dt.Columns.Add(col.Id, GetType(col.type));
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                throw ex;
            }

        }

        public static Type GetType(string _type)
        {
            Type type = null;
            switch (_type)
            {
                case "int":
                    type = typeof(int);
                    break;
                case "double":
                    type = typeof(double);
                    break;
                case "datetime":
                    type = typeof(DateTime);
                    break;
                default:
                    type = typeof(string);
                    break;
            }

            return type;
        }

        public static List<ColumnConfigEntity> GetColumnsEntityByName(string sheetConfigName)
        {

            var sheetconfig = DataService.ExcelSheetsConfigs.Sheets.FirstOrDefault((s) => s.SheetName.Equals(sheetConfigName));
            if (sheetconfig == null)
                return null;
            else
                return sheetconfig.Columns;
        }

        public static string GetColumnsHeaderById(string sheetConfigName, string id)
        {

            var sheetconfig = DataService.ExcelSheetsConfigs.Sheets.FirstOrDefault((s) => s.SheetName.Equals(sheetConfigName));
            if (sheetconfig == null)
                return "无";
            else
                return sheetconfig.Columns.FirstOrDefault((c) => c.Id.Equals(id)).HeadText;
        }
        public static ColumnConfigEntity GetColumnById(string sheetConfigName, string id)
        {

            var sheetconfig = DataService.ExcelSheetsConfigs.Sheets.FirstOrDefault((s) => s.SheetName.Equals(sheetConfigName));
            if (sheetconfig == null)
                return null;
            else
                return sheetconfig.Columns.FirstOrDefault((c) => c.Id.Equals(id));
        }
        public static int UsersLogin(string userName, string password)
        {
            int result = -1; // error
            try
            {
                var user = SysConfigs.Users.FirstOrDefault((u) => u.UserName.Equals(userName));
                if (user != null)
                {
                    if (Encode(password).Equals(user.PassWord))
                    {
                        result = 0;  //login success
                    }
                    else
                    {
                        result = 1;// password error
                    }
                }
                else
                {
                    result = 2;//username doesn't exist
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                result = -1;
            }

            return result;
        }

        public static int ChangePassword(string userName, string password)
        {
            int result = -1; // error
            try
            {
                int index = SysConfigs.Users.FindIndex((u) => u.UserName.Equals(userName));
                if (index > -1)
                {

                    SysConfigs.Users[index].PassWord = Encode(password);
                    Save();
                    result = 0;
                }

            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                result = -1;
            }

            return result;
        }

        /// <summary>
        /// Convert string to int.
        /// If fail to convert, 0 will be returned.
        /// </summary>
        /// <param name="str">The string to convert.</param>
        /// <returns>The converted int value.</returns>
        public static int StringToInt(string str)
        {
            return StringToInt(str, 0);
        }
        /// <summary>
        /// Convert string to int.
        /// </summary>
        /// <param name="str">The string to convert.</param>
        /// <param name="defaultValue">If fail to convert, defaultValue will be returned.</param>
        /// <returns>The converted int value.</returns>
        public static int StringToInt(string str, int defaultValue)
        {
            int.TryParse(str, out defaultValue);
            return defaultValue;
        }
        /// <summary>
        /// Executes a http web request using "Post" method and returns the responsed string.
        /// </summary>
        /// <param name="url">The url to request.</param>
        /// <param name="postData">The data to be post.</param>
        /// <returns>The responsed string.</returns>
        public static string HttpPost(string url, string postData)
        {
            return HttpPost(url, postData, "utf-8", null);
        }

        /// <summary>
        /// Executes a http web request using "Post" method and returns the responsed string.
        /// </summary>
        /// <param name="url">The url to request.</param>
        /// <param name="postData">The data to be post.</param>
        /// <param name="encodingName">The encoding name.</param>
        /// <param name="referer">The referer url or let it empty.</param>
        /// <returns>The responsed string.</returns>
        public static string HttpPost(string url, string postData, string encodingName, string referer)
        {
            try
            {
                var request = (HttpWebRequest)HttpWebRequest.Create(url);

                var encoding = Encoding.GetEncoding(encodingName);
                var dataBytes = encoding.GetBytes(postData ?? string.Empty);

                request.AllowAutoRedirect = true;
                request.Timeout = 3600;
                request.Method = "POST";
                request.Headers["Cache-Control"] = "no-cache";
                request.Accept = "*/*";
                request.ContentType = "application/x-www-form-urlencoded";
                request.ContentLength = dataBytes.Length;
                request.UserAgent = "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506; .NET CLR 3.5.21022)";

                if (!string.IsNullOrEmpty(referer))
                {
                    request.Referer = referer;
                }

                using (var stream = request.GetRequestStream())
                {
                    stream.Write(dataBytes, 0, dataBytes.Length);
                    stream.Close();
                }

                using (var response = (HttpWebResponse)request.GetResponse())
                {
                    var stream = response.GetResponseStream();

                    using (var reader = new StreamReader(stream, encoding))
                    {
                        string content = reader.ReadToEnd();

                        return content;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                return ex.Message;
            }
        }

        #region 错误提示的公共方法

        public static void ShowErrorMsg(string message)
        {
            MessageBox.Show(message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        #endregion

        #region 计算农历日期的方法
        public static string getChineseDate()
        {
            string Chinesedate = "";
            string YearSky = "";
            string YearEarth = "";
            string Month = "";
            string Day = "";
            ChineseLunisolarCalendar calendar = new ChineseLunisolarCalendar();
            int year = calendar.GetYear(DateTime.Now);
            int month = calendar.GetMonth(DateTime.Now);
            int day = calendar.GetDayOfMonth(DateTime.Now);

            //计算天干
            //YearSky = calendar.GetSexagenaryYear(DateTime.Now).ToString();
            //计算与指定日期对应的甲子（60 年）循环中的年。如2010年计算得到27，对应60甲子的庚寅
            string[] Sky = { "癸", "乙", "丙", "丁", "戊", "己", "庚", "辛", "壬", "甲" };
            for (int i = 0; i < 10; i++)
            {
                if ((year - 3) % 10 == i)
                {
                    YearSky = Sky[i - 1];
                    break;
                }
            }
            //计算地支
            string[] Earth = { "亥", "丑", "寅", "卯", "辰", "巳", "午", "未", "申", "酉", "戌", "子" };
            for (int i = 0; i < 12; i++)
            {
                if ((year - 3) % 12 == i)
                {
                    YearEarth = Earth[i - 1];
                    break;
                }
            }
            //计算月份
            string[] Months = { "元", "二", "三", "四", "五", "六", "七", "八", "九", "十", "十一", "十二", "预留闰月", "预留索引避免越界" };
            //闰月情况为十三个月
            //EastAsianLunisolarCalendar.GetMonth方法返回一个介于 1 到 13 之间的数字来表示与指定日期关联的月份。
            //如果在一年的八月和九月之间有一个闰月，则 GetMonth 方法为八月返回 8，为闰八月返回 9，为九月返回 10。
            int add_month = calendar.GetLeapMonth(year);
            if (add_month > 0)
            {
                for (int i = 13; i >= add_month; i--)
                {
                    Months[i] = Months[i - 1];
                }
                Months[add_month - 1] = "闰" + Months[add_month - 2];
            }
            for (int i = 0; i <= 13; i++)
            {
                if (month == i)
                {
                    Month = Months[i - 1];
                    break;
                }
            }
            //计算日期           
            string[] Days ={"初一","初二","初三","初四","初五","初六","初七","初八","初九","初十",
                           @"十一","十二","十三","十四","十五","十六","十七","十八","十九","二十",
                           @"二十一","二十二","二十三","二十四","二十五","二十六","二十七","二十八","二十九","三十"};

            for (int i = 0; i < 30; i++)
            {
                if (day == i)
                {
                    Day = Days[i - 1];
                    break;
                }
            }
            Chinesedate = YearSky + YearEarth + "年" + Month + "月" + Day + "日";
            return Chinesedate;
        }
        #endregion
        #region MD5Encript

        /// <summary>
        /// MD5加密
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string MD5Encript(string str)
        {
            return MD5Encript(str, "utf-8");
        }

        /// <summary>
        /// MD5加密
        /// </summary>
        /// <param name="str"></param>
        /// <param name="encodingName"></param>
        /// <returns></returns>
        public static string MD5Encript(string str, string encodingName)
        {
            MD5 md5csp = new MD5CryptoServiceProvider();
            Encoding encoding = Encoding.GetEncoding(encodingName);

            return BitConverter.ToString(md5csp.ComputeHash(encoding.GetBytes(str))).Replace("-", string.Empty);
        }


        #endregion

        #region DES字符串加密解密的方法
        private const string KEY_64 = "7th-long";//注意了，是8个字符，64位
        private const string IV_64 = "12-5peng";
        public static string Encode(string data)
        {
            byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(KEY_64);
            byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(IV_64);
            DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
            int i = cryptoProvider.KeySize;
            MemoryStream ms = new MemoryStream();
            CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateEncryptor(byKey, byIV), CryptoStreamMode.Write);
            StreamWriter sw = new StreamWriter(cst);
            sw.Write(data);
            sw.Flush();
            cst.FlushFinalBlock();
            sw.Flush();
            return Convert.ToBase64String(ms.GetBuffer(), 0, (int)ms.Length);
        }
        public static string Decode(string data)
        {
            byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(KEY_64);
            byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(IV_64);
            byte[] byEnc;
            try
            {
                byEnc = Convert.FromBase64String(data);
            }
            catch
            {
                return null;
            }
            DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
            MemoryStream ms = new MemoryStream(byEnc);
            CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateDecryptor(byKey, byIV), CryptoStreamMode.Read);
            StreamReader sr = new StreamReader(cst);
            return sr.ReadToEnd();
        }
        #endregion

        /// <summary>
        /// 将c# DateTime时间格式转换为Unix时间戳格式
        /// </summary>
        /// <param name="time">时间</param>
        /// <returns></returns>
        public static long DateTimeToUnix(DateTime time)
        {
            DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1));

            return (long)(time - startTime).TotalSeconds;
        }

        public static T Clone<T>(T RealObject)
        {
            using (Stream objectStream = new MemoryStream())
            {
                //利用 System.Runtime.Serialization序列化与反序列化完成引用对象的复制
                IFormatter formatter = new BinaryFormatter();
                formatter.Serialize(objectStream, RealObject);
                objectStream.Seek(0, SeekOrigin.Begin);
                return (T)formatter.Deserialize(objectStream);
            }
        }
        #region 考勤业务
        /// <summary>
        /// 缺点是太慢
        /// </summary>
        /// <param name="fileKey"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static System.Data.DataTable GetTableBySheetName(string fileKey, string sheetName)
        {

            var fileCach = Caching.Cache[fileKey];
            if (fileCach == null || !((Dictionary<string, System.Data.DataTable>)fileCach).Keys.Contains(sheetName))
            {
                DataService.ReadExcel(fileKey, sheetName, out sheetName);
            }
            fileCach = Caching.Cache[fileKey];
            return ((Dictionary<string, System.Data.DataTable>)fileCach)[sheetName];
        }

        /// <summary>
        ///按照实际数据查询
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="tableName"></param>
        /// <param name="dgv"></param>
        /// <returns></returns>
        public static bool GetAttendanceBySheetName(string strFilePath, string sheetName, out string attendresult, DataGridView dgv, string currentSheet)
        {
            if (dgv == null || dgv.Rows.Count < 1)
            {
                attendresult = sheetName;
                return true;
            }
            bool result = false;
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook;// 这里抛出异常= new Microsoft.Office.Interop.Excel.Workbook();
            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
            Microsoft.Office.Interop.Excel.Range range;
            object objFilePath;
            object oMissing = System.Reflection.Missing.Value;

            if (!System.IO.File.Exists(strFilePath))
            {
                throw new System.IO.FileNotFoundException();
            }
            try
            {
                xlApp.Visible = false;
                objFilePath = strFilePath;
                object Nothing = System.Reflection.Missing.Value;
                object password = (object)DataService.SysConfigs.ExcelPassword;
                xlWorkbook = xlApp.Workbooks.Open(strFilePath, Nothing, Nothing, Nothing, password, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);

                List<string> allsheets = new List<string>();
                for (int i = 1; i <= xlWorkbook.Sheets.Count; i++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[i];
                    allsheets.Add(wsheet.Name);
                }


                //打开文件中的在配置信息中的表数据

                if (string.IsNullOrEmpty(sheetName))
                {
                    xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[1];
                }
                else if (allsheets.IndexOf(sheetName) != -1)
                {
                    xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[sheetName];
                }
                else
                {
                    throw new Exception("not find " + sheetName);
                }
                attendresult = xlWorksheet.Name;
                int rowCount = 474;
                //int rowCount = xlWorksheet.UsedRange.Rows.Count;
                int rowStart = 1;
                int colCount = 36;
                //下面全部要改写成按照grid内容查询
                //得到表格结构
                IList<AttendanceConfigEntity> AttendanceList = new List<AttendanceConfigEntity>();
                for (int i = 1; i <= rowCount; i++)
                {
                    if (cvt.ToInt(((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, strUtil.GetExcelColName(4)]).Value2) == 1
                        && cvt.ToInt(((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, strUtil.GetExcelColName(5)]).Value2) == 2
                        && cvt.ToInt(((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i, strUtil.GetExcelColName(6)]).Value2) == 3)
                    {
                        AttendanceConfigEntity attend = new AttendanceConfigEntity();
                        attend.StartRowIndex = i;
                        attend.DeptName = cvt.ToNotNull(((Microsoft.Office.Interop.Excel.Range)xlWorksheet.Cells[i - 1, strUtil.GetExcelColName(2)]).Value2);
                        AttendanceList.Add(attend);
                        if (AttendanceList.Count > 1)
                        {
                            AttendanceList[AttendanceList.Count - 2].EndRowIndex = i - 1;
                        }
                    }
                }
                if (AttendanceList.Count > 1)
                {
                    AttendanceList[AttendanceList.Count - 1].EndRowIndex = rowCount;
                }

                //遍历datagridview，根据当前表与用户名查询部门表数据
                //根据当前表格去更新考勤
                if (!string.IsNullOrEmpty(currentSheet))
                {

                    logger.Debug("======================================导入开始=========================");
                    string deptName = currentSheet;
                    System.Data.DataTable currentDataSource = (System.Data.DataTable)dgv.DataSource;
                    string userNames = "";//没有查询的员工名字
                    string selectUserNames = "";//查询到的员工名字
                    List<int> HasSelectRows = new List<int>();
                    List<int> GridSelectRows = new List<int>();
                    int NotFind = 0;
                    int HaveFind = 0;
                    bool ExistDept = false;
                    //调试信息，正式注释
                    //for (int i = 4; i < 35; i++)
                    //{
                    //    logger.Debug(string.Format("UserName:[X,{0}]",strUtil.GetExcelColName(i)));
                    //}
                    for (int j = 0; j < currentDataSource.Rows.Count; j++)
                    {
                        string userName = currentDataSource.Rows[j]["UserName"].ToString();
                        int Index = -1;
                        foreach (var attend in AttendanceList)
                        {
                            if (Index != -1)
                                break;
                            if (deptName.IndexOf(attend.DeptName) != -1)
                            {
                                ExistDept = true;
                                for (int i = attend.StartRowIndex + 2; i < attend.EndRowIndex; i += 3)
                                {
                                    //避免重复去比较，已经匹配了的，则忽略掉比较
                                    if (HasSelectRows.Contains(i))
                                        continue;                                   
                                    if (cvt.ToNotNull(((Range)xlWorksheet.Cells[i, strUtil.GetExcelColName(1)]).Value2).Trim().Replace(" ", "") == userName)
                                    {
                                        Index = i;
                                        HasSelectRows.Add(i);
                                        break;
                                    }
                                }

                            }

                        }
                        if (Index != -1)
                        {
                            //logger.Debug(string.Format("UserName:[{0},{1}]", Index, strUtil.GetExcelColName(1)));
                            //实际出勤
                            double realWorkDay = 0;
                            string memo = "";
                            //列查询
                            for (int i = 4; i < 35; i++)
                            {
                                //上午出勤上午是3.5 下午是4.5  3.5/8小时=0.44，  4.5/8小时=0.56

                                if (cvt.ToNotNull(((Range)xlWorksheet.Cells[Index, strUtil.GetExcelColName(i)]).Value2).Trim() == "√")
                                {
                                    realWorkDay += 0.44;
                                }
                                if (cvt.ToNotNull(((Range)xlWorksheet.Cells[Index+1, strUtil.GetExcelColName(i)]).Value2).Trim() == "√")
                                {
                                    realWorkDay += 0.56;
                                }
                            }

                            double otherAdd = 0;
                            double oneHourse = 0;
                            double oneFiveHourse = 0;
                            string[] overTime = new string[] { cvt.ToNotNull(((Range)xlWorksheet.Cells[Index, strUtil.GetExcelColName(36)]).Value2).Trim(),
                    cvt.ToNotNull(((Range)xlWorksheet.Cells[Index+1, strUtil.GetExcelColName(36)]).Value2).Trim(),
                     cvt.ToNotNull(((Range)xlWorksheet.Cells[Index+2, strUtil.GetExcelColName(36)]).Value2).Trim()
                    };
                            //调试信息
                            //logger.Debug(string.Format("UserName:{0},OverTime:[{1},{2}]:" + overTime[0] + ",[{3},{4}]:" + overTime[1] + ",[{5},{6}]:" + overTime[2], userName, Index, strUtil.GetExcelColName(36), Index + 1, strUtil.GetExcelColName(36), Index + 2, strUtil.GetExcelColName(36)));
                            foreach (var s in overTime)
                            {
                                if (s.IndexOf("*4") != -1)
                                {
                                    if (!s.StartsWith("*"))
                                        otherAdd = cvt.ToDouble(s.Split('*')[0]);
                                }
                                if (s.IndexOf("*8") != -1)
                                {
                                    if (!s.StartsWith("*"))
                                        oneHourse = cvt.ToDouble(s.Split('*')[0]);
                                }
                                if (s.IndexOf("*10") != -1)
                                {
                                    if (!s.StartsWith("*"))
                                        oneFiveHourse = cvt.ToDouble(s.Split('*')[0]);
                                }
                            }
                            if (currentDataSource.Columns.Contains("RealWorkDay"))
                                currentDataSource.Rows[j]["RealWorkDay"] = realWorkDay;
                            if (currentDataSource.Columns.Contains("OneWorkHour"))
                                currentDataSource.Rows[j]["OneWorkHour"] = oneHourse;
                            if (currentDataSource.Columns.Contains("OneFiveWorkHour"))
                                currentDataSource.Rows[j]["OneFiveWorkHour"] = oneFiveHourse;
                            if (currentDataSource.Columns.Contains("OtherPayAdd"))
                                currentDataSource.Rows[j]["OtherPayAdd"] = otherAdd;
                            string memos = "";
                            double DelEat = 0;
                            if (currentDataSource.Columns.Contains("Memo"))
                            {
                                memo = cvt.ToNotNull(((Range)xlWorksheet.Cells[Index, strUtil.GetExcelColName(35)]).Value2).Trim();
                                memos += memo + ",";
                                if (memo != "" && currentDataSource.Rows[j]["Memo"].ToString().IndexOf(memo) == -1)
                                {
                                    currentDataSource.Rows[j]["Memo"] += memo;
                                }
                               
                                memo = cvt.ToNotNull(((Range)xlWorksheet.Cells[Index+1, strUtil.GetExcelColName(35)]).Value2).Trim();
                                memos += memo + ",";
                                if (memo != "" && currentDataSource.Rows[j]["Memo"].ToString().IndexOf(memo) == -1)
                                {
                                    currentDataSource.Rows[j]["Memo"] += memo;
                                }
                                memo = cvt.ToNotNull(((Range)xlWorksheet.Cells[Index+2, strUtil.GetExcelColName(35)]).Value2).Trim();
                                memos += memo + ",";
                                if (memo != "" && currentDataSource.Rows[j]["Memo"].ToString().IndexOf(memo) == -1)
                                {
                                    currentDataSource.Rows[j]["Memo"] += memo;
                                }
                                //扣伙食，memos：外宿,扣100元,,|,,,|外宿,,,
                                if (currentDataSource.Columns.Contains("DelEat"))
                                {
                                   
                                    Match match = Regex.Match(memos, @"扣\d+");
                                    if (match != null)
                                    {
                                        DelEat = PayManage.cvt.ToDouble(match.Value.Replace("扣", ""));
                                    }
                                    currentDataSource.Rows[j]["DelEat"] = DelEat;
                                }
                                //logger.Debug(string.Format("UserName:{0},Memo:[{1},{2}]:" + memos.Split(',')[0] + ",[{3},{4}]:" + memos.Split(',')[1] + ",[{5},{6}]:" + memos.Split(',')[2], userName, Index, strUtil.GetExcelColName(35), Index + 1, strUtil.GetExcelColName(35), Index + 2, strUtil.GetExcelColName(35)));
                            }
                            HaveFind += 1;
                            selectUserNames += "["+userName+"]" + string.Format("实际出勤：{0},1.0加班小时：{1},1.5加班小时：{2},夜班天数：{3},备注：{4} .扣伙食：{5}"
                        , realWorkDay, oneHourse, oneFiveHourse, otherAdd, memos, DelEat) + ",";
                            logger.Debug(string.Format("["+userName+"]实际出勤：{0},1.0加班小时：{1},1.5加班小时：{2},夜班天数：{3},备注：{4} .扣伙食：{5}" , realWorkDay, oneHourse, oneFiveHourse, otherAdd, memos,DelEat));
                            if (HaveFind % 6 == 0)
                            {
                                selectUserNames += "\r\n";
                            }
                            GridSelectRows.Add(j);
                        }
                        else
                        {
                            userNames +="["+ userName + "],";
                            NotFind += 1;
                            if (NotFind % 6 == 0)
                            {
                                userNames += "\r\n";
                            }
                        }
                    }
                    if (NotFind == 0 && HaveFind > 0)
                    {
                        attendresult = "{"+currentSheet+"}部门 "+"全部人导入考勤数据，合计：" + HaveFind + "人导入考勤,,分别是\r\n" + selectUserNames;
                    }
                    else if (NotFind > 0 && HaveFind > 0)
                    {
                        attendresult = "{" + currentSheet + "}部门 " + "合计：" + HaveFind + "人导入考勤," + NotFind + "人没有导入考勤,分别是\r\n" + userNames + "\r\n导入考勤人：" + selectUserNames;
                    }
                    else if (HaveFind == 0)
                    {
                        if (!ExistDept)
                        {
                            attendresult = "{" + currentSheet + "}部门 " + "考勤表格中不存在" + currentSheet + "中的考勤部门，请核实！";
                        }
                        else
                        {
                            attendresult = "{" + currentSheet + "}部门 " + "没有匹配任何人的考勤,合计：" + NotFind + "人没有导入考勤,分别是" + userNames;
                        }
                    }
                    logger.Debug(attendresult);
                    logger.Debug("======================================导入结束=========================");
                    if (HaveFind > 0)
                    {
                       dgv.DataSource = currentDataSource;
                        for (int i = 0; i < GridSelectRows.Count; i++)
                        {
                            dgv.Rows[GridSelectRows[i]].Cells["UserName"].Style.ForeColor = System.Drawing.Color.Red;
                            dgv.Rows[GridSelectRows[i]].Cells["UserName"].Style.BackColor = System.Drawing.Color.Green;
                        }
                    }
                }
                else
                {
                   throw new Exception("请选择当前表格！");
                }

                xlWorkbook.Close(oMissing, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);

            }
            catch (Exception ex)
            {
                result = false;
                logger.Error(ex.Message);
                throw ex;

            }
            finally
            {

                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();

            }
            return result;
        }
        #endregion 考勤业务
        /*
         2.4 sheet的重命名

view sourceprint?1 _wsh.Name = "xxx"; 

         

3. 删除行和列  

3.1 删除行：


view sourceprint?1 ((Range)_wsh.Rows[3, Missing.Value]).Delete(XlDeleteShiftDirection.xlShiftUp); 


3.2 删除列：

view sourceprint?1 _wsh.get_Range(  

2 _wsh.Cells[1, 2],   

3 _wsh.Cells[_wsh.Rows.Count, 2]).Delete(XlDeleteShiftDirection.xlShiftToLeft  

4 ); 

         

4. 添加行和列  

4.1 添加行：


view sourceprint?1 ((Range)_wsh.Rows[11, Missing.Value])  

2 .Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); 


4.2 添加列：

view sourceprint?1 _wsh.get_Range(  

2 _wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1])  

3 .Insert(Missing.Value, XlInsertShiftDirection.xlShiftToRight); 

         * 5. 单元格操作  

5.1 单元格的取得


view sourceprint?1 //获得单元格对象  

2 _wsh.Cells[row, cell] 


5.2 设置公式


view sourceprint?1 //在对应的单元格输入公式即可  

2 _wsh.Cells[row, cell] = "=Sum(A1/B1)"; 


5.3 合并单元格


view sourceprint?1 ((Range)_wsh.Rows[1, Missing.Value]).Merge(Missing.Value); 


5.4 设置行高和列宽


view sourceprint?1 ((Range)_wsh.Rows[3, Missing.Value]).RowHeight = 5;  

2 ((Range)_wsh.Rows[3, Missing.Value]).ColumnWidth = 5; 


5.5 设置单元格颜色 颜色共有56中，详情请参照附录的[颜色对照表]


view sourceprint?1 ((Range)_wsh.Rows[1, Missing.Value]).Interior.ColorIndex = 3; 


5.6 设置字号


view sourceprint?1 ((Range)_wsh.Cells[1, "B"]).Font.Size = 8; 


5.7 是否设置粗体


view sourceprint?1 ((Range)_wsh.Rows[1, Missing.Value]).Font.Bold = false; 


5.8 单元格/区域、水平垂直居中


view sourceprint?1 ((Range)_wsh.Cells[2, 1]).HorizontalAlignment = XlVAlign.xlVAlignCenter; 


5.9 设置区域边框


view sourceprint?1 ((Range)_wsh.Cells[3, 3]).Borders.LineStyle = 3; 


5.10 设置边框的上、下、左、右线条


view sourceprint?01 //左  

02 _wsh.get_Range(  

03 _wsh.Cells[2, 1], _wsh.Cells[2, 2])  

04 .Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//  

05    

06 //右  

07 _wsh.get_Range(  

08 _wsh.Cells[2, 1], _wsh.Cells[2, 2])  

09 .Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//  

10    

11 //上  

12 _wsh.get_Range(  

13 _wsh.Cells[2, 1], _wsh.Cells[2, 2])  

14 .Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//下  

15    

16 //下  

17 _wsh.get_Range(  

18 _wsh.Cells[2, 1], _wsh.Cells[2, 2])  

19 .Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick; 


6. 指定区域的复制 


view sourceprint?01 _Worksheet _wsh = (_Worksheet)shs.get_Item(1);//复制选中区域的内容  

02    

03 Range range = _wsh.get_Range(_wsh.Cells[7, 1], _wsh.Cells[10, _wsh.Columns.Count]);  

04    

05 range.Select();  

06 range.Copy(Type.Missing);  

07    

08 //选中粘贴的起始位置  

09 Range test = ((Range)_wsh.Cells[11, 1]);  

10 test.Select();  

11    

12 //屏蔽掉Alert，默认确定粘贴  

13 app.DisplayAlerts = false;  

14 test.Parse(Missing.Value, Missing.Value); 

文件保存


view sourceprint?1 //屏蔽掉系统跳出的Alert  

2 app.AlertBeforeOverwriting = false;  

3    

4 //保存到指定目录  

5 SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); 


注：这个地方只能采用该方法保存，不然在指定路径下保存文件外，在我的文档中也会生成一个对应的副本 

7.2 后续处理：退出和释放


view sourceprint?1 //_wbk.Close(null, null, null);  

2 //wbks.Close();  

3 app.Quit();  

4    

5 //释放掉多余的excel进程  

6 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);   

7 app = null; 
若单元格已经设置为下拉框


view sourceprint?1 //这里的“1，2，3”设置的就是下拉框的值  

2 ((Range)_wsh.Cells[2, 1])  

3 .Validation.Modify(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, Type.Missing, "1,2,3", Type.Missing); 


8.2 若单元格还没有设置为下拉框的形式


view sourceprint?1 ((Range)_wsh.Cells[2, 1])  

2 .Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, Type.Missing,"1,2,3", Type.Missing); 


8.3 取得下拉框域的值


view sourceprint?1 string strValue = ((Range)_wsh.Cells[2, 1]).Validation.Formula1; 


注：若在excel模板中通过有效性设定了下拉框的值，strValue得到的将会是excel里的公式，需将其转换, 取得strValue后，可以根据其索引得到你需要的数值； 

 

9. 隐藏行和隐藏列  

9.1 隐藏行


view sourceprint?1 _wsh.get_Range(_wsh.Cells[19, 1], _wsh.Cells[22, 1]).EntireRow.Hidden = true; 


9.2 隐藏列


view sourceprint?1 _wsh.get_Range(_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1])  

2 .EntireColumn.Hidden = true; 

  


                 
 */

    }
}
